﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Entities;
using System.Data.SqlClient;

namespace DAL
{
    public class DAL_Users
    {
        DBConnection db = new DBConnection();
        En_Users eu = new En_Users();
        List<En_Facility> Lef= new List<En_Facility>();
        En_Facility ef = new En_Facility();
        En_UserType eut = new En_UserType();
        DataTable dt;
        DataSet ds;
        public En_Users getUserbyName(String userName)
        {
            SqlParameter[] arrPar = {new SqlParameter("@userName",eu.UserName)};
            arrPar[0].Value = userName;
            dt = db.getDataTable("sp_GetUsersbyName",arrPar);
            foreach (DataRow dr in dt.Rows)
            {
                eu = new En_Users(dr["UserID"].ToString(), dr["UserName"].ToString(), dr["UserPassword"].ToString(), dr["UserFullName"].ToString(), Convert.ToInt32(dr["UserAge"].ToString()), dr["UserGender"].ToString(), dr["UserEmail"].ToString(), Convert.ToInt32(dr["TypeID"].ToString()),Convert.ToBoolean(dr["LoginStatus"].ToString()));
            }
            return eu;

        }
        
        public String getTypeName(int TypeID)
        {
            String s = "";
            SqlParameter[] arrPar = { new SqlParameter("@TypeID", eut.TypeID) };
            arrPar[0].Value = TypeID;
            dt = db.getDataTable("sp_getTypeNameByTypeID", arrPar);
            foreach (DataRow dr in dt.Rows)
            {
                s = dr["TypeName"].ToString();
            }
            return s;
        }
        public int getNameLogin(string UserName)
        {
            int intType = 0;
            SqlParameter[] arrPar = { new SqlParameter("@UserName", eu.UserName) };
            arrPar[0].Value = UserName;
            dt = db.getDataTable("sp_GetNameLogin", arrPar);
            if (dt.Rows.Count > 0)
            {
                intType = int.Parse("0" + dt.Rows[0]["TypeID"]);
            }
            return intType;
        }

        public int checkLogin(String username, String password)
        {
            int intResult = 0;
            En_Users eu = new En_Users();
            SqlParameter[] arrPar = { new SqlParameter("@UserName", eu.UserName), new SqlParameter("@UserPassword", eu.UserPassword) };
            arrPar[0].Value = username;
            arrPar[1].Value = password;
            dt = db.getDataTable("sp_CheckLogin", arrPar);
            if (dt.Rows.Count > 0)
            {
                intResult = int.Parse("0" + dt.Rows[0][0]);
            }
            return intResult;
        }
        public void UpDateProfileUser(String UserName,String UserFullName,int UserAge,String UserGender,String UserEmail)
        {
            En_Users eu = new En_Users();
            SqlParameter[] arrPar = { new SqlParameter("@UserName", eu.UserName), new SqlParameter("@userFullName", eu.UserFullName), new SqlParameter("@userAge", eu.UserAge), new SqlParameter("@UserGender", eu.UserGender), new SqlParameter("@UserEmail", eu.UserEmail) };
            arrPar[0].Value = UserName;
            arrPar[1].Value = UserFullName;
            arrPar[2].Value = UserAge;
            arrPar[3].Value = UserGender;
            arrPar[4].Value = UserEmail;
            db.UpdateData("sp_updateUserByName", arrPar);
        }
        public List<En_Facility> ShowFacility()
        {
            Lef = new List<En_Facility>();
            dt = db.getData("sp_GetFacility");
            foreach (DataRow dr in dt.Rows)
            {
                Lef.Add(new En_Facility(Convert.ToInt32( dr["FacilityID"].ToString()), dr["FacilityName"].ToString()));

            }
            return Lef;
        }
        public void InSertFacility(String FacilityName)
        {
            En_Facility ef = new En_Facility();
            SqlParameter[] arrPar = { new SqlParameter("@FacilityName", ef.FacilityName) };
            arrPar[0].Value = FacilityName;
            db.UpdateData("sp_InsertFacility", arrPar);
        }
        public DataSet ShowallUser()
        {
            ds = new DataSet();
            ds = db.getDataSet("sp_ShowALlUser");
            return ds;
        }
        public void InsertUser(String UserID, String UserName,String UserPassword, String UserFullName, int UserAge, String UserGender, String UserEmail, int TypeID, int LoginStatus)
        {
            eu = new En_Users();
            SqlParameter[] arrPar = { new SqlParameter("@UserID", eu.UserID), new SqlParameter("@UserName", eu.UserName), new SqlParameter("@UserPassword", eu.UserPassword), new SqlParameter("@UserFullName", eu.UserFullName), new SqlParameter("@UserAge", eu.UserAge), new SqlParameter("@UserGender", eu.UserGender), new SqlParameter("@UserEmail", eu.UserEmail), new SqlParameter("@TypeID", eu.TypeID), new SqlParameter("@LoginStatus", eu.LoginStatus) };
            arrPar[0].Value = UserID;
            arrPar[1].Value = UserName;
            arrPar[2].Value = UserPassword;
            arrPar[3].Value = UserFullName;
            arrPar[4].Value = UserAge;
            arrPar[5].Value = UserGender;
            arrPar[6].Value = UserEmail;
            arrPar[7].Value = TypeID;
            arrPar[8].Value = LoginStatus;
            db.UpdateData("sp_InsertUsers",arrPar);
        }
        public int getTypeIDbyTypeName(String TypeName)
        {
            eut = new En_UserType();
            int TypeID = 0; 
            SqlParameter[] arrPar = { new SqlParameter("@TypeName",eut.TypeName)};
            arrPar[0].Value = TypeName;
            dt = db.getDataTable("sp_getTypeIDbyTypeName", arrPar);
            foreach (DataRow dr in dt.Rows)
            {
                TypeID = Convert.ToInt32(dr["TypeID"].ToString());
            }
            return TypeID;
        }
        public DataSet getAllUserTypeName()
        {
            ds = new DataSet();
            ds = db.getDataSet("sp_getAllUserTypeName");
            return ds;
        }
        
        

    }
}
